--*----------------------------------------------------------------------------
--
-- ParseImageLink
--
--*----------------------------------------------------------------------------
IF EXISTS (
  SELECT 1
    FROM sys.objects
   WHERE type = 'FN'
     AND schema_id = SCHEMA_ID('dbo')
     AND name = 'ParseImageLink'
)
DROP FUNCTION ParseImageLink
GO
--*----------------------------------------------------------------------------
CREATE FUNCTION dbo.ParseImageLink
(
  @String NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
BEGIN
  DECLARE
    @Count INT
  , @Result NVARCHAR(MAX)
  SET @Result = LTRIM(RTRIM(@String))
  IF (@Result LIKE '[[]URL%')
  BEGIN
    SET @Count = CHARINDEX('[IMG]', @Result)
    IF @Count = 0
       RETURN @String
    SET @Result = SUBSTRING(@Result, @Count+5, LEN(@Result))
    SET @Count = CHARINDEX('[/IMG]', @Result)
    IF @Count = 0
       RETURN @String
    SET @Result = SUBSTRING(@Result, 1, @Count-1)
  END
  IF (@Result LIKE '<a%')
  BEGIN
    SET @Count = CHARINDEX('<img', @Result)
    IF @Count = 0
       RETURN @String
    SET @Result = SUBSTRING(@Result, @Count, LEN(@Result))
  END
  IF (@Result LIKE '<img%')
  BEGIN
    SET @Count = CHARINDEX('src', @Result)
    IF @Count = 0
       RETURN @String
    SET @Result = SUBSTRING(@Result, @Count+3, LEN(@Result))
    SET @Count = CHARINDEX('"', @Result)
    IF @Count = 0
       RETURN @String
    SET @Result = SUBSTRING(@Result, @Count+1, LEN(@Result))
    SET @Count = CHARINDEX('"', @Result)
    IF @Count = 0
       RETURN @String
    SET @Result = SUBSTRING(@Result, 1, @Count-1)
  END

  IF @Result LIKE '%photo.qip.ru%'
  BEGIN
    SET @Result = REPLACE(@Result, 'xlarge','[%size%]')
    SET @Result = REPLACE(@Result, 'large','[%size%]')
    SET @Result = REPLACE(@Result, 'middle','[%size%]')
    SET @Result = REPLACE(@Result, 'small','[%size%]')
  END
  ELSE
  IF @Result LIKE '%fotki.yandex.ru%'
  BEGIN
    SET @Result = REPLACE(@Result, '_XXS.','_[%size%].')
    SET @Result = REPLACE(@Result, '_XS.','_[%size%].')
    SET @Result = REPLACE(@Result, '_M.','_[%size%].')
    SET @Result = REPLACE(@Result, '_L.','_[%size%].')
    SET @Result = REPLACE(@Result, '_XL.','_[%size%].')
    SET @Result = REPLACE(@Result, '_XXL.','_[%size%].')
    SET @Result = REPLACE(@Result, '_XXXL.','_[%size%].')
    SET @Result = REPLACE(@Result, '_ORIG.','_[%size%].')
  END

  RETURN @Result
END
GO
